<?php

/**
 * EGGMS 销售统计
 * ============================================================================
 * 网站地址: http://www.eggms.com；
 * ============================================================================
 * $Author: 王海龙 $
 * $Id: sale_satts.php 17217 2014-09-11 08:45 王海龙 $
*/

define('IN_ECS', true);

require(dirname(__FILE__) . '/includes/init.php');
require_once(ROOT_PATH . 'includes/lib_order.php');
require_once(ROOT_PATH . 'languages/' .$_CFG['lang']. '/admin/statistic.php');

$smarty->assign('lang', $_LANG);

/* act操作项的初始化 */
if (empty($_REQUEST['act']))
{
    $_REQUEST['act'] = 'list';
}
else
{
    $_REQUEST['act'] = trim($_REQUEST['act']);
}

/*------------------------------------------------------ */
//--订单统计
/*------------------------------------------------------ */
if ($_REQUEST['act'] == 'list')
{
    admin_priv('sale_order_stats');
    
    $end_date = $_REQUEST['end_date']?$_REQUEST['end_date']:(date("Y-m-d"));//结束日期
    $start_date = $_REQUEST['start_date']?$_REQUEST['start_date']:(date("Y-m-d",strtotime('-10 day',strtotime($end_date))));//开始日期
    $start_time = strtotime($start_date );//开始时间戳
    $end_time = strtotime($end_date );//结束时间戳
    $regions = $_REQUEST['regions']?$_REQUEST['regions']:array();//需统计地区

    /* 随机的颜色数组 */
    $color_array = array('33FF66', 'FF6600', '3399FF', '009966', 'CC3399', 'FFCC33', '6699CC', 'CC3366', 'FF0000', '00FF00', '0000FF');

    /* 获取所有省份 */
    $sql = "SELECT region_id, region_name FROM " . $ecs->table('region'). " WHERE region_type = 1";
    $region_arr = $db->getAll($sql);
    /* 设置省份是否被选取 */
    foreach ($region_arr as $key => $value) {
        if(in_array($value['region_id'],$regions)){
            $region_arr[$key]['checked'] = 1;
        }
    }
    /* 计算订单各种费用之和的语句 */
    $total_fee = " SUM(goods_amount + shipping_fee) AS total_turnover ";
    /* 取得订单转化率数据 */
    $sql = "SELECT COUNT(*) AS total_order_num, " .$total_fee.
           " FROM " . $ecs->table('order_info').
           " WHERE add_time >= $start_time AND add_time <= $end_time AND order_status != 2 AND order_status != 3 AND (pay_id = 3 OR (pay_id = 1 AND pay_status = 2))";
    $order_general = $db->getRow($sql);

    // $sql = "SELECT * FROM " . $ecs->table('order_info') . " WHERE add_time >= $start_time AND add_time <= $end_time AND order_status != 2 AND order_status != 3 AND (pay_id = 3 OR (pay_id = 1 AND pay_status = 2))";
    // $order_list = $db->getAll($sql);

    //订单详情
    $data = get_profit($start_time, $end_time);

    //detail($data);

    /* 赋值到模板 */
    $smarty->assign('order_general',       $order_general);

    $smarty->assign('ur_here',             "销售统计");
    $smarty->assign('start_date',          $start_date);
    $smarty->assign('end_date',            $end_date);
    $smarty->assign('region_arr',          $region_arr);
    $smarty->assign('region_stats',        get_region_rate($start_time, $end_time)); //地区订单情况
    $smarty->assign('amount_interval',     get_amount_interval($data,$regions));  //各地区价格区间
    $smarty->assign('order_date',          get_order_date($start_time, $end_time, $regions)); //下单日期分布
    $smarty->assign('pay_region',          get_pay_region($start_time, $end_time, $regions)); // 支付-地区分布
    $smarty->assign('profit',              get_region_profit($data, $regions));//地区盈亏
    $smarty->assign('buy_way',             buy_way($start_time, $end_time, $regions) );     //购买渠道
    $smarty->assign('user_stats',          get_user_stats($start_time, $end_time) );   //用户统计分析
    $smarty->assign('user_buy_count',      get_user_buy_count($start_time,$end_time));  //用户购买次数统计
    $smarty->display('sale_stats.htm');
    get_gift_cost($start_time, $end_time);
    
}

/*------------------------------------------------------ */
//--订单统计需要的函数
/*------------------------------------------------------ */
 /**
  * 取得订单概况数据(包括订单的几种状态)
  * @param       $start_date    开始查询的日期
  * @param       $end_date      查询的结束日期
  * @return      $order_info    订单概况数据
  */
 function get_orderinfo($start_date, $end_date)
 {
    $order_info = array();

    /* 未确认订单数 */
    $sql = 'SELECT COUNT(*) AS unconfirmed_num FROM ' .$GLOBALS['ecs']->table('order_info').
           " WHERE order_status = '" .OS_UNCONFIRMED. "' AND add_time >= '$start_date'".
           " AND add_time < '" . ($end_date + 86400) . "'";

    $order_info['unconfirmed_num'] = $GLOBALS['db']->getOne($sql);

    /* 已确认订单数 */
    $sql = 'SELECT COUNT(*) AS confirmed_num FROM ' .$GLOBALS['ecs']->table('order_info').
           " WHERE order_status = '" .OS_CONFIRMED. "' AND shipping_status NOT ". db_create_in(array(SS_SHIPPED, SS_RECEIVED)) . " AND pay_status NOT" . db_create_in(array(PS_PAYED, PS_PAYING)) ." AND add_time >= '$start_date'".
           " AND add_time < '" . ($end_date + 86400) . "'";
    $order_info['confirmed_num'] = $GLOBALS['db']->getOne($sql);

    /* 已成交订单数 */
    $sql = 'SELECT COUNT(*) AS succeed_num FROM ' .$GLOBALS['ecs']->table('order_info').
           " WHERE 1 " . order_query_sql('finished') .
           " AND add_time >= '$start_date' AND add_time < '" . ($end_date + 86400) . "'";
    $order_info['succeed_num'] = $GLOBALS['db']->getOne($sql);

    /* 无效或已取消订单数 */
    $sql = "SELECT COUNT(*) AS invalid_num FROM " .$GLOBALS['ecs']->table('order_info').
           " WHERE order_status > '" .OS_CONFIRMED. "'".
           " AND add_time >= '$start_date' AND add_time < '" . ($end_date + 86400) . "'";
    $order_info['invalid_num'] = $GLOBALS['db']->getOne($sql);
    return $order_info;
 }

/**
 * 订单地区分布
 * @param  [type] $start_date [description]
 * @param  [type] $end_date   [description]
 * @return array  各省的订单情况
 */
function get_region_rate($start_time, $end_time)
{
    //有效订单条件
    $condition = " WHERE add_time >= $start_time AND add_time <= $end_time AND order_status != 2 AND order_status != 3 AND (pay_id = 3 OR (pay_id = 1 AND pay_status = 2))";
    /* 所有订单和 */
    $sql = "SELECT COUNT(*) AS succeed_num FROM " .$GLOBALS['ecs']->table('order_info') . $condition;
    $all = $GLOBALS['db']->getOne($sql);

    /* 各省订单数 */
    $sql = "SELECT r.region_name, r.region_id, o.succeed_num FROM" .
        " (SELECT * FROM " .$GLOBALS['ecs']->table('region')." WHERE region_type =1) as r" .
        " LEFT JOIN" . 
        " (SELECT province, COUNT(*) AS succeed_num FROM " .$GLOBALS['ecs']->table('order_info') . $condition . " GROUP BY province ) as o" . 
        " ON r.region_id=o.province ORDER BY succeed_num DESC, r.region_id ASC";
    $res = $GLOBALS['db']->query($sql);

    $region_stats = array();
    //全国
    $region_stats = $region_stats[0]['id']       = $row['region_id'];
    $region_stats[0]['name']     = '全国';
    $region_stats[0]['sum']      = $all;
    $region_stats[0]['rate']     = 100;
    //各省
    $i = 1;
    while ( $row = $GLOBALS['db']->fetchRow($res) ) {
      $region_stats[$i]['id']       = $row['region_id'];
      $region_stats[$i]['name']     = $row['region_name'];
      $region_stats[$i]['sum']      = ($row['succeed_num'])? $row['succeed_num'] : 0;
      $region_stats[$i++]['rate']     = sprintf('%.4f',$row['succeed_num']/$all)*100;
    }

    //仓库发货情况
    $sql = "SELECT area_name AS area, count(*) AS sum FROM " . $GLOBALS['ecs']->table('order_info') . " AS o ," .
            $GLOBALS['ecs']->table('service_area'). "AS s" . 
            $condition . "AND agency_id=area_id GROUP BY agency_id";

    $stock = $GLOBALS['db']->getAll($sql);
    $region_stats['stock'] = $stock;

    return $region_stats;
}

/**
 * 获得价格区间（订单金额统计）
 * @param  [data] 订单原始数据
 * @param  array $region 存放地区id的数组
 * @return [type]         [description]
 */
function get_amount_interval($data, $regions)
{
    //返回数组
    $res = array();   

    //初始价格区间
    $interval = array('1' => array(0, 49),
                      '2' => array(50, 99),
                      '3' => array(100, 149),
                      '4' => array(150, 199),
                      '5' => array(200, 249),
                      '6' => array(250, 299),
                      '7' => array(300, 349),
                      '8' => array(350, 399),
                      '9' => array(400, 10000)
                 );
    $index = 0;   //返回的价格区间索引
    foreach ($data as $dkey => $dvalue) {
        //二分查找价格区间
        // $mid = ($min+$max)/2;
        $min = 1; $max = 9;
        while($min <= $max){
            $mid = intval( ($min+$max)/2 );
            if($dvalue['order_amount'] >= $interval[$mid][0] && $dvalue['order_amount'] <= $interval[$mid][1]){
                $index = $mid;
                break;
            }elseif($dvalue['order_amount'] > $interval[$mid][1]){
                $min = $mid + 1; 
            }else{
                $max = $mid -1;
            }
        }

        //订单统计
        $res[$index]['cnt'] += 1;  //该区间总订单量
        $res[$index]['amount'] += $dvalue['order_amount']; //该区间总金额
        $res[$index]['all_pure'] += $dvalue['pure'];   //该区间的总利润
        if($dvalue['pure'] > 0){
            $res[$index]['profit_cnt'] += 1;        //盈利单数
            $res[$index]['profit_pure'] += $dvalue['pure']; //盈利金额
            $res[$index]['profit_order'][] = $dvalue['order_id'];  //订单号（点击后显示）
        }else{
            $res[$index]['deficit_cnt'] += 1;
            $res[$index]['deficit_pure'] += $dvalue['pure'];
            $res[$index]['deficit_order'][] = $dvalue['order_id'];
        }
    }
    
    ksort($res);
    return $res;  
}

/* 下单日期统计 */
function get_order_date($start_time, $end_time, $regions)
{
    $time = array();    //时间段
    if($end_time-$start_time <= 86400){
        //一天（时）
        $start_time = strtotime(date('Y-m-d', $start_time));
        for ($i=0; $i < 24; $i++) { 
            $time[$i]['start_time'] = $start_time + $i*3600 ;
            $time[$i]['end_time'] = $time[$i]['start_time']+3600-1 ;
            $time[$i]['date'] = date('H',$time[$i]['start_time']);
        }
    }else{
        //一月（天）
        $date1 = date('Y-m-d', $start_time);
        $date2 = date('Y-m-d', $end_time);
        $i=0; 
        while ( strtotime($date2) >= strtotime("+".$i." day",strtotime($date1) ) ) {
            $date = strtotime( date("Y-m-d",strtotime("+".$i++." day",strtotime($date1))) ); 
            $time[$i]['start_time'] = mktime(0, 0, 0, date('m',$date), date('d',$date), date('Y',$date));
            $time[$i]['end_time'] = mktime(0, 0, 0, date('m', $date), date('d', $date)+1, date('Y', $date))-1;
            $time[$i]['date'] = date('m-d',$time[$i]['start_time']);
        }
    }
    //有效订单条件
    $condition = " WHERE order_status != 2 AND order_status != 3 AND (pay_id = 3 OR (pay_id = 1 AND pay_status = 2))";
    //构造省市条件where...
    $pro_condition[1] = $condition; //全国
    foreach ($regions as $key => $value) {
        $pro_condition[$value] = $condition . " AND o.province=$value";  
    }

    //统计情况
    $return = array();
    foreach ($pro_condition as $pkey => $pvalue) {
        $count = 0;
        $return[$pkey]['region'] = selectaddress($pkey);
        foreach ($time as $tkey => $tvalue) {
            $sql = "SELECT count(*) as cnt" .
                " FROM " . $GLOBALS['ecs']->table('order_info') ." as o" . $pvalue .
                " AND add_time >= ". $tvalue['start_time'] ." AND add_time <= ".$tvalue['end_time'] ;
            $return[$pkey][$tkey] = $GLOBALS['db']->getOne($sql);
            $count += $return[$pkey][$tkey];
        }
        $return[$pkey]['count'] = $count;
    }
    $return['data'] = $return;
    $return['time'] = $time;
    return $return;
}
/**
 * [get_pay_region 获取支付-地区分布数据]
 * @param  [type] $start_time [开始时间]
 * @param  [type] $end_time   [结束时间]
 * @param  [type] $regions    [地址]
 * @return [type] array         [返回数组]
 */
function get_pay_region($start_time, $end_time, $regions){
    $order_count = 0;
    $sw = '1';// 初始化搜索条件
    $sw .= " AND order_status != 2 AND order_status != 3 AND (pay_id = 3 OR (pay_id = 1 AND pay_status = 2))";

    $sql = "SELECT pay_id,pay_name,count(*) AS count FROM " .  $GLOBALS['ecs']->table('order_info') . " WHERE $sw AND add_time >= $start_time AND add_time < $end_time GROUP BY pay_id";
    foreach ($GLOBALS['db']->getAll($sql) as $key => $value) {
        $data[0]['pay'][$value['pay_id']]['count'] = $value['count'];
        $data[0]['pay'][$value['pay_id']]['pay_name'] = $value['pay_name'];
        $data[0]['pay'][$value['pay_id']]['pay_id'] = $value['pay_id'];
        $order_count += $value['count'];
    }
    $data[0]['count'] = $order_count;
    $data[0]['area'] = '全国';
    $data[0]['pct'] = '100%';
    /* 计算百分比 */
    foreach ($data[0]['pay'] as $key => $value) {
        $data[0]['pay'][$key]['pct'] = $data[0]['pay'][$key]['pct_all'] = sprintf('%.2f%%',$value['count']/$order_count*100);
    }

    if($regions) {
        foreach ($regions as $key => $value) {
            $region_count = 0;
            $sql = "SELECT pay_id,pay_name,province,count(*) AS count FROM " .  $GLOBALS['ecs']->table('order_info') . " WHERE $sw AND province = $value AND add_time >= $start_time AND add_time < $end_time GROUP BY pay_id";
            $res = $GLOBALS['db']->getAll($sql);
            foreach ($res as $k => $v) {
                $data[$value]['pay'][$v['pay_id']]['count'] = $v['count'];
                $data[$value]['pay'][$v['pay_id']]['pay_name'] = $v['pay_name'];
                $data[$value]['pay'][$v['pay_id']]['pay_id'] = $v['pay_id'];
                $region_count += $v['count'];
            }
            /* 计算百分比 */
            $data[$value]['count'] = $region_count;
            $data[$value]['area'] = selectaddress($value);
            $data[$value]['pct'] = sprintf('%.2f%%',$region_count/$order_count*100);
            if(!$data[$value]['pay']) break;
            foreach ($data[$value]['pay'] as $k => $v) {
                $data[$value]['pay'][$k]['pct'] = sprintf('%.2f%%',$v['count']/$region_count*100);
                $data[$value]['pay'][$k]['pct_all'] = sprintf('%.2f%%',$v['count']/$order_count*100);
            }
        }
    }
    return $data;
}

/**
 * 盈亏统计
 * @param  [type] $start_time [开始时间]
 * @param  [type] $end_time   [结束时间]
 * @param  [type] $regions    [地址]
 * @return [type] array       [返回数组]
 */
function get_profit($start_time, $end_time)
{
    //各地区的邮费
    include_once('includes/shipping_fee_rule.php');
    //有效订单条件
    $condition = " order_status != 2 AND order_status != 3 AND (pay_id = 3 OR (pay_id = 1 AND pay_status = 2))";

    $sql = "SELECT gl.order_sn,gl.order_id, gl.add_time, gl.province, gl.agency_id, gl.pay_id, gl.goods_amount+gl.shipping_fee AS order_amount,count(*) AS goodscount, sum(g.goods_weight*gl.goods_number) AS weight, sum(gs.goods_xprice*gl.goods_number) AS xprice" .
           " FROM (SELECT oi.order_sn ,oi.order_id, oi.add_time, oi.province, case oi.agency_id when '0' then '1' when '1' then '1' when '2' then '2' end as agency_id, oi.pay_id, oi.goods_amount, oi.shipping_fee, og.goods_sku, og.goods_number" .
                    " FROM egg_order_info AS oi LEFT JOIN egg_order_goods AS og  ON oi.order_id=og.order_id".
                    " WHERE oi.add_time >= $start_time AND oi.add_time <= $end_time AND".$condition.") AS gl".
           " LEFT JOIN egg_goods AS g ON gl.goods_sku=g.goods_sku".
           " LEFT JOIN egg_goods_stock AS gs ON gl.goods_sku=gs.goods_sku and gl.agency_id=gs.area_id".
           " GROUP BY gl.order_id";
    $res = $GLOBALS['db']->query($sql);
    
    $order_profit = array(); //每张单的利润情况
    while ( $row = $GLOBALS['db']->fetchRow($res) ) {
        $index = $row['order_id'];                  //数组索引值
        $order_profit[$index]['order_id']     = $index;           //订单id
        $order_profit[$index]['order_sn']     = $row['order_sn']; //订单号
        $order_profit[$index]['order_amount'] = $row['order_amount']; //订单总金额
        $order_profit[$index]['add_time']     = $row['add_time'];    //订单时间
        $order_profit[$index]['province']     = $row['province'];  //订单地区
        $order_profit[$index]['weight']       = $row['weight']; //商品重量
        $order_profit[$index]['agency_id']    = $row['agency_id'];  //发货地区   广东1 杭州2
        $order_profit[$index]['pay_id']       = $row['pay_id'];  //支付方式 到付为3
        /* 计算邮费 */
        $base_weight = ($row['province'] == '2132') ? 5 : 1;     //如果是广东，则首重是5斤
    
        if($row['weight'] <= $base_weight){
            $order_profit[$index]['shipping_fee'] = $shipping[$row['province']]['skg'];
        }else{
            $order_profit[$index]['shipping_fee'] = $shipping[$row['province']]['skg'] + $shipping[$row['province']]['xkg']*ceil($row['weight']-$base_weight);
        }


        //判断是否到付
        if($row['pay_id'] == 3){
            $extra = 0;
            if($row['agency_id'] == 1){
                $extra = 3;
            }else if($row['agency_id'] == 2 && $row['order_amount'] < 335){
                $extra = 5;
            }else if($row['agency_id'] == 2 && $row['order_amount'] >= 335){
                $extra = $row['order_amount'] * 0.015;
            }
            $order_profit[$index]['shipping_fee'] += $extra;
        }
        $order_profit[$index]['shipping_fee'] = sprintf('%.3f',$order_profit[$index]['shipping_fee']);
        $order_profit[$index]['xprice']  = $row['xprice']; //商品进货价
        $order_profit[$index]['box']     = 5.087;          //纸箱成本
        $order_profit[$index]['package'] = sprintf('%.3f',$row['order_amount']*0.0087);  //包装成本
        $order_profit[$index]['gross']   = $row['order_amount']-$row['xprice'];      //毛利润
        $order_profit[$index]['pure']    = sprintf('%.3f', $row['order_amount']-($row['xprice']+5.087+$order_profit[$index]['package']+$order_profit[$index]['shipping_fee']) );     //纯利润

    }

    return $order_profit;
}

/**
 * 对获得的所有数据进行分析处理
 * 按省划分
 * @param  [type] $start_time [description]
 * @param  [type] $end_time   [description]
 * @param  [type] $regions    [description]
 * @return [type]             [description]
 */
function get_region_profit($data, $regions)
{
      
    $res = array();
    $res[0]['area']   = '全国';  
    $res[0]['summary']['all']['order_sn'] = ' 汇 总 ';
    foreach ($data as $key => $value) { 
        /* 全国 */
        if($value['pure'] > 0){
            $res[0]['profit'][] = $value;  //盈单
        }else{
            $res[0]['deficit'][] = $value;  //亏单
        }
        
        /* 每天 */
        $date = date('Y-m-d',$value['add_time']);  //日期
        $res[0]['summary'][$date]['order_sn'] = $date;
        $res[0]['summary']['all']['order_amount'] += $value['order_amount'] ; $res[0]['summary'][$date]['order_amount'] += $value['order_amount'] ; 
        $res[0]['summary']['all']['xprice']       += $value['xprice'] ;       $res[0]['summary'][$date]['xprice']       += $value['xprice'] ;
        $res[0]['summary']['all']['shipping_fee'] += $value['shipping_fee'] ; $res[0]['summary'][$date]['shipping_fee'] += $value['shipping_fee'] ;
        $res[0]['summary']['all']['box']          += $value['box'] ;          $res[0]['summary'][$date]['box']          += $value['box'] ;
        $res[0]['summary']['all']['package']      += $value['package']  ;     $res[0]['summary'][$date]['package']      += $value['package']  ;
        $res[0]['summary']['all']['gross']        += $value['gross'] ;        $res[0]['summary'][$date]['gross']        += $value['gross'] ;
        $res[0]['summary']['all']['pure']         += $value['pure'] ;         $res[0]['summary'][$date]['pure']         += $value['pure'] ;
        
        //盈亏单统计
        if($value['pure']>=0){
            $res[0]['summary']['all']['profit']  += 1;
            $res[0]['summary']['all']['profit_pure'] += $value['pure'];
            $res[0]['summary'][$date]['profit']  += 1;
            $res[0]['summary'][$date]['profit_pure'] += $value['pure'];
        }else{
            $res[0]['summary']['all']['deficit'] += 1;
            $res[0]['summary']['all']['deficit_pure'] += $value['pure'];
            $res[0]['summary'][$date]['deficit'] += 1;
            $res[0]['summary'][$date]['deficit_pure'] += $value['pure'];
        }
        /* 各省 */
        if( in_array($value['province'], $regions) ){
            if(!isset($res[$value['province']]['area'])){
                $res[$value['province']]['area'] = selectaddress($value['province']);
                $res[$value['province']]['summary']['all']['order_sn'] = ' 汇 总 ';
            }
            if($value['pure'] > 0){
                $res[$value['province']]['profit'][] = $value; //盈单
            }else{
                $res[$value['province']]['deficit'][] = $value;//亏单
            }
                                                                                                  $res[$value['province']]['summary'][$date]['order_sn'] = $date;
            $res[$value['province']]['summary']['all']['order_amount'] += $value['order_amount']; $res[$value['province']]['summary'][$date]['order_amount'] += $value['order_amount'];
            $res[$value['province']]['summary']['all']['xprice']       += $value['xprice'];       $res[$value['province']]['summary'][$date]['xprice'] += $value['xprice'];
            $res[$value['province']]['summary']['all']['shipping_fee'] += $value['shipping_fee']; $res[$value['province']]['summary'][$date]['shipping_fee'] += $value['shipping_fee'];
            $res[$value['province']]['summary']['all']['box']          += $value['box'];          $res[$value['province']]['summary'][$date]['box'] += $value['box'];
            $res[$value['province']]['summary']['all']['package']      += $value['package'];      $res[$value['province']]['summary'][$date]['package'] += $value['package'];
            $res[$value['province']]['summary']['all']['gross']        += $value['gross'];        $res[$value['province']]['summary'][$date]['gross'] += $value['gross'];
            $res[$value['province']]['summary']['all']['pure']         += $value['pure'];         $res[$value['province']]['summary'][$date]['pure'] += $value['pure'];

            //盈亏单统计
            if($value['pure']>=0){
                $res[$value['province']]['summary']['all']['profit']  += 1;
                $res[$value['province']]['summary']['all']['profit_pure'] += $value['pure'];
                $res[$value['province']]['summary'][$date]['profit']  += 1;
                $res[$value['province']]['summary'][$date]['profit_pure'] += $value['pure'];
            }else{
                $res[$value['province']]['summary']['all']['deficit'] += 1;
                $res[$value['province']]['summary']['all']['deficit_pure'] += $value['pure'];
                $res[$value['province']]['summary'][$date]['deficit'] += 1;
                $res[$value['province']]['summary'][$date]['deficit_pure'] += $value['pure'];
            }
        }
    }
    
    return $res;
}

function detail($data)
{
    foreach ($data as $key => $value) {
        echo  "<br/>" . $value['order_sn'] ."\t". $value['xprice'] ."\t". $value['shipping_fee'] ."\t". floatval($value['box']+$value['package']) ."\t". $value['order_amount'];
    }
}
/**
 * 赠品、补发成本
 */
function get_gift_cost($start_time, $end_time)
{
    $condition = " order_status != 2 AND order_status != 3 AND (pay_id = 3 OR (pay_id = 1 AND pay_status = 2))";

    $sql = "SELECT og.order_id, gs.goods_xprice,og.goods_number,og.is_gift FROM " .
           "( SELECT * FROM egg_order_goods where order_id IN " .
                "( SELECT order_id FROM egg_order_info AS oi " .
                " WHERE oi.add_time >= $start_time AND oi.add_time <= $end_time AND".$condition.") " . 
            "and goods_price=0 ) AS og".
           " LEFT JOIN egg_goods_stock AS gs ON og.goods_sku=gs.goods_sku and area_id=1";

    $res = $GLOBALS['db']->getAll($sql);
    
    $return['gift'] = 0;    //赠品
    $return['resend'] = 0;  //补发
    foreach ($res as $key => $value) {
        if($value['is_gift'] == 1){
            $return['gift'] += $value['goods_xprice'] * $value['goods_number'];    
        }else{
            $return['resend'] += $value['goods_xprice'] * $value['goods_number'];  
        }
    }
    var_dump($return);
    return $return;
}

/**
 * 购买渠道分析
 * @param  [type] $start_time [description]
 * @param  [type] $end_time   [description]
 * @param  [type] $regions    [description]
 * @return [type]             [description]
 */
function buy_way($start_time, $end_time, $regions)
{
    //有效订单条件
    $condition = " WHERE add_time >= $start_time AND add_time <= $end_time AND order_status != 2 AND order_status != 3 AND (pay_id = 3 OR (pay_id = 1 AND pay_status = 2))";
    //构造省市条件where...
    $pro_condition[1] = $condition; //全国
    foreach ($regions as $key => $value) {
        $pro_condition[$value] = $condition . " AND province=$value";  
    }

    //统计情况
    $return = array();
    foreach ($pro_condition as $key => $value) {
        $sql = "SELECT from_ad, count(order_id) AS cnt, sum(goods_amount+shipping_fee) AS amount FROM " .$GLOBALS['ecs']->table('order_info') . $value . " GROUP BY from_ad";
        $return[$key]['stats'] = $GLOBALS['db']->getAll($sql);
        $return[$key]['region'] = selectaddress($key);
    }

    return $return;
}

/**
 * 全国，不做省市划分(第一次购买人数)
 * @param  [type] $start_time [description]
 * @param  [type] $end_time   [description]
 * @return [type]             [description]
 */
function get_user_stats($start_time, $end_time)
{
    $sql = "SELECT * FROM ".
    "( SELECT user_id,add_time FROM " .$GLOBALS['ecs']->table('order_info') . " WHERE order_status != 2 AND order_status != 3 AND (pay_id = 3 OR (pay_id = 1 AND pay_status = 2)) GROUP BY user_id ORDER BY add_time) AS ua" .
    " WHERE add_time >= $start_time AND add_time <= $end_time";

    $time = array();
    $date1 = date('Y-m-d', $start_time);
    $date2 = date('Y-m-d', $end_time);
    $i=0; 
    while ( strtotime($date2) >= strtotime("+".$i." day",strtotime($date1) ) ) {
        $date = strtotime( date("Y-m-d",strtotime("+".$i++." day",strtotime($date1))) ); 
        $time[$i]['start_time'] = mktime(0, 0, 0, date('m',$date), date('d',$date), date('Y',$date));
        $time[$i]['end_time'] = mktime(0, 0, 0, date('m', $date), date('d', $date)+1, date('Y', $date))-1;
        $time[$i]['date'] = date('m-d',$time[$i]['start_time']);
    }

    $res = $GLOBALS['db']->query($sql);
    $user_stats = array();
    $i = 0;
    while( $row = $GLOBALS['db']->fetchRow($res) )
    {
        $user_stats['all']['count'] +=1;
        if($row['add_time'] >= $time[$i]['start_time'] && $row['add_time'] <= $time[$i]['end_time']){
            $user_stats[$time[$i]['date']]['count'] += 1;
        }else{
            $i++;
            $user_stats[$time[$i]['date']]['count'] += 1;
        }
    }

    return $user_stats;
}

/**
 * 用户购买次数统计分析
 * @param  [type] $start_time [description]
 * @param  [type] $end_time   [description]
 * @return [type]             [description]
 */
function get_user_buy_count($start_time, $end_time)
{
    $sql = "SELECT temp.cnt, count(*) AS counts FROM (" . 
            "SELECT user_id, count(*) AS cnt FROM " .$GLOBALS['ecs']->table('order_info') . 
            " WHERE add_time >= $start_time AND add_time <= $end_time AND order_status != 2 AND order_status != 3 AND (pay_id = 3 OR (pay_id = 1 AND pay_status = 2)) GROUP BY user_id " .
            ") AS temp GROUP BY temp.cnt ";
    $res = $GLOBALS['db']->getAll($sql);
    
    return $res; 
}



?>